package finelProject;

import java.lang.reflect.Type;
import java.util.Collection;

import com.google.gson.reflect.TypeToken;

import finelProject.model.Messages;
import finelProject.model.User;

public class MyAppConstants {
	//derby constants
		public final String DB_NAME = "UsersDB";
		public final static String DB_DATASOURCE = "java:comp/env/jdbc/UserDatasource";
		public final String PROTOCOL = "jdbc:derby:"; 
		
		
		public final static String FOLLOW = "follow";
		public final static String INSERT = "insert";
		public final static String SEPARATOR = "-";
		
		//sql statements
		public final static String CREATE_USERS_TABLE = "CREATE TABLE USERS(UserName varchar(10),"
				+ "Password varchar(8),"
				+ "NickName varchar(20),"
				+ "ShortDescription varchar(50),"
				+ "SetPhotoURL varchar(100),"
				+ "PRIMARY KEY (UserName))";
		public final static String CREATE_MESSAGES_TABLE = "CREATE TABLE MESSAGES(Topic varchar(139),"
				+ "Author varchar(20),"
				+ "RepublishNumber INTEGER,"
				+ "TimeOfPublish TIMESTAMP,"
				+ "Content varchar(140),"
				+ "MessageId INTEGER,"
				+ "AutorNomberOfFollowers INTEGER,"
				+ "Popularty REAL,"
				+ "HtmlTopicVersion varchar(500),"
				+ "PRIMARY KEY (MessageId))";
		public final static String CREATE_FOLLOW_TABLE = "CREATE TABLE FOLLOW(Follower varchar(20),"
				+ "Followe varchar(20),"
				+ "PRIMARY KEY (Follower,Followe))";
		public final static String SUM_NUMBER_OF_FOLLOWERS = "SELECT COUNT(*) "
					 + "FROM FOLLOW "
					 + "WHERE Followe=? "
					 + "GROUP BY Followe";
		public final static String SUM_NUMBER_OF_FOLLOWES = "SELECT COUNT(*) "
				 + "FROM FOLLOW "
				 + "WHERE Follower=? "
				 + "GROUP BY Followe";
		public final static String INSERT_INTO_FOLLOW = "INSERT INTO FOLLOW(Follower,Followe)"
				+ "VALUES(?,?)";
		public final static String INSERT_INTO_USERS = "INSERT INTO USERS VALUES(?,?,?,?,?)";
		
		public final static String GET_USER			 = "SELECT * FROM USERS "
				+ "WHERE UserName=?";
		public final static String PUBLISH_MESSAGE = "INSERT INTO MESSAGES VALUES(?,?,?,?,?,?,?,?,?)";
		public final static String SELECT_WHO_USER_FOLLOWS = "SELECT * FROM USERS "
			+ "WHERE UserName IN("
							   + "SELECT Followe"
							   + "FROM FOLLOW"
							   + "WHERE Follower=?)";
		public final static String SELECT_WHO_FOLLOW_USER = "SELECT * FROM USERS "
				+ "WHERE UserName IN("
								   + "SELECT Followe"
								   + "FROM FOLLOW"
								   + "WHERE Followe=?)";
		public final static Type USER_COLLECTION = new TypeToken<Collection<User>>() {}.getType();
		public final static Type MESSAGES_COLLECTION = new TypeToken<Collection<Messages>>() {}.getType();
		public final static Type MESSAGE = new TypeToken<Messages>() {}.getType();
		public final static String GET_MESSAGE			 = "SELECT * FROM MESSAGES "
				+ "WHERE MessageId=?";
		public final static String GET_MESSAGES_BY_AOTUR_NAME	= "SELECT * FROM MESSAGES "
				+ "WHERE Author=?";
		public final static String UPDATE_REPULISH_NUMBER = "UPDATE MESSAGES "
														 + "SET RepublishNumber=?,"
														 + "Popularty=?"
														 + "WHERE MessageId=?";
		public final static String UPDATE_AUTOR_FOLLOERS_NUMBER = "UPDATE MESSAGES "
				 + "SET AutorNomberOfFollowers=?, "
				 + "Popularty=? "
				 + "WHERE MessageId=?";
		public final static String GET_TEN_LAST_MESSAGES = "SELECT * FROM MESSAGES "
														 + "ORDER BY TimeOfPublish DESC "
														 + "FETCH FIRST 10 ROWS ONLY";
		
}
